package com.zkah.myfirstpoi.controller;

import com.zkah.myfirstpoi.pojo.User;
import com.zkah.myfirstpoi.service.UserService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.xmlbeans.impl.xb.xsdschema.Public;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@Controller
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;
    @RequestMapping("/list")
    public String selectAllUser(Model model){
        List<User> userList = userService.selectAllUser();
        model.addAttribute("userList",userList);
        return "jikuoji";
    }
    @RequestMapping("/export")
    @ResponseBody
    public void export(HttpServletResponse response) throws IOException {
        List<User> userList = userService.selectAllUser();
        //创建Excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建第一个sheet页 并命名
        HSSFSheet sheet = wb.createSheet("获取excel测试表格");
        HSSFRow row = null;
        row=sheet.createRow(0);//创建第一行
        //行高
        row.setHeight((short)(26.25*20));
        //为第一行单元格设值
        row.createCell(0).setCellValue("用户信息列表");
        /*为标题设计空间
         * firstRow从第1行开始
         * lastRow从第0行结束
         *
         *从第1个单元格开始
         * 从第3个单元格结束
         */
        //合并单元格 起始行 结束行 起始列 结束列
        CellRangeAddress rowRegion = new CellRangeAddress(0,0,0,2);
        sheet.addMergedRegion(rowRegion);
        CellStyle cellStyle = wb.createCellStyle();
/*        CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
        sheet.addMergedRegion(columnRegion);*/

        /*
         * 动态获取数据库列 sql语句 select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='user' and table_schema='test'
         * 第一个table_name 表名字
         * 第二个table_name 数据库名称
         * */

        row = sheet.createRow(1);
        row.setHeight((short) (22.50 * 20));//设值行高
        row.createCell(0).setCellValue("用户ID");//为第一个单元格设值
        row.createCell(1).setCellValue("用户名");//第二个
        row.createCell(2).setCellValue("用户密码");//第三个
        for(int i=0;i<userList.size();i++){
            row = sheet.createRow(i+2);
            User user = userList.get(i);
            row.createCell(0).setCellValue(user.getId());//为第一个单元格设值
            row.createCell(1).setCellValue(user.getUsername());//为第二个单元格设值
            row.createCell(2).setCellValue(user.getPassword());//为第三个单元格设值
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        //列宽自适应
        for(int i=0;i<=13;i++){
            sheet.autoSizeColumn(i);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=user.xls");//默认Excel名称
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();
    }
    @RequestMapping("/import")
    public String exImport(@RequestParam(value = "filename")MultipartFile file){
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = userService.batchImport(fileName,file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "redirect:list";
    }

}
